 /**
   * Copyright 2014 Viettel Telecom. All rights reserved.
   * VIETTEL PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
   */
 package com.viettel.core.dao.cms; 
import java.util.ArrayList;
import java.util.List;

import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;

import com.viettel.core.common.utils.StringUtil;
import com.viettel.core.entities.cms.Article;
import com.viettel.core.entities.cms.CmsUser;
import com.viettel.core.entities.cms.Media;
import com.viettel.core.entities.cms.Province;
import com.viettel.core.entities.vo.MediaSearchFilter;
import com.viettel.core.entities.vo.MediaSearchVO;
import com.viettel.core.enumtype.ArticleStatus;
import com.viettel.core.enumtype.MediaStatus;
import com.viettel.core.enumtype.UserStatus;
import com.viettel.core.exceptions.DataAccessException;
import com.viettel.core.repo.IRepository;
 /**
   *  Mo ta muc dich cua lop (interface)
   *  @author: TOANTM1
   *  @version:  
   *  @since: 27/02/2014 - 11:34:02
   */
 public class MediaDAOImpl implements MediaDAO { 
	 private IRepository repo; 
		
	 	public void setRepo(IRepository repo) {
			this.repo = repo;
		} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public Media getMediaById(Integer id) throws DataAccessException { 
		return repo.getEntityById(Media.class, id); 
	} 
 	
 	
 	
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public Media createMedia(Media media) 
			throws DataAccessException { 
		if (media == null) { 
			throw new IllegalArgumentException("media"); 
		} 
		repo.create(media); 
		return repo.getEntityById(Media.class, media.getId()); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public void updateMedia(Media media) throws DataAccessException { 
		if (media == null) { 
			throw new IllegalArgumentException("media"); 
		} 
		repo.update(media); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public void deleteMedia(Media media) throws DataAccessException { 
		if (media == null) { 
			throw new IllegalArgumentException("media"); 
		} 
		repo.delete(media); 
	}

 	/**
 	 * @author tuantm20
 	 * @throws DataAccessException
 	 */
	@Override
	public List<MediaSearchVO> getListMediaByFilter(MediaSearchFilter filter)
			throws DataAccessException {
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();
		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();
		
		selectSql.append(" SELECT ME.id  	as id, ");
		selectSql.append(" ME.name 			as name, ");
		selectSql.append(" ME.address 		as address, ");
		selectSql.append(" ME.code 			as code, ");
		selectSql.append(" ME.description 	as description, ");
		selectSql.append(" ME.status 		as status, ");
		selectSql.append(" ME.province_id 	as provinceId, ");
		selectSql.append(" ME.district_id 	as districtId, ");
		selectSql.append(" ME.ward_id 		as wardId, ");
		selectSql.append(" PR.name 			as provinceName, ");
		selectSql.append(" DI.name 			as districtName, ");
		selectSql.append(" WA.name 			as wardName, ");
		selectSql.append(" US.username 		as createdUser, ");
		selectSql.append(" ME.created_time 	as createdTime ");
		
		fromQuery.append(" FROM MEDIA ME ");
		fromQuery.append(" 		JOIN CMS_USER US ");
		fromQuery.append(" 			ON ME.cms_user_id = US.id ");
		fromQuery.append(" 		LEFT JOIN PROVINCE PR ");
		fromQuery.append(" 			ON ME.province_id = PR.id ");
		fromQuery.append(" 		LEFT JOIN DISTRICT DI ");
		fromQuery.append(" 			ON ME.district_id = DI.id ");
		fromQuery.append(" 		LEFT JOIN WARD WA ");
		fromQuery.append(" 			ON ME.ward_id = WA.id ");
		
		fromQuery.append(" where ME.status != ? ");
		paramSelect.add(MediaStatus.DELETED.getValue());
		countParams.add(MediaStatus.DELETED.getValue());
		
		if (filter.getId() != null) {
			fromQuery.append(" and ME.id = ? ");
			paramSelect.add(filter.getId());
			countParams.add(filter.getId());
		}
		
		if (filter.getNameText() != null) {
			fromQuery.append(" and (upper(ME.name_text) like upper(?) ESCAPE '/' )");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getNameText())+ "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getNameText())+ "%");
		}
		
		if (filter.getAddress() != null) {
			fromQuery.append(" and (upper(ME.address_text) like upper(?) ESCAPE '/' )");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getAddressText())+ "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getAddressText())+ "%");
		}
		
		if (filter.getStatus() != null) {
			fromQuery.append(" and ME.status = ? ");
			paramSelect.add(filter.getStatus());
			countParams.add(filter.getStatus());
		}
		
		if (filter.getProvinceId() != null) {
			fromQuery.append(" and ME.province_id = ? ");
			paramSelect.add(filter.getProvinceId());
			countParams.add(filter.getProvinceId());
		}
		
//		if(filter.getFromDate()!=null && filter.getToDate()!=null ){
//			fromQuery.append("  AND TRUNC(ME.CREATED_TIME) BETWEEN TRUNC(?) AND TRUNC(?) ");
//			paramSelect.add(filter.getFromDate());
//			countParams.add(filter.getFromDate());
//			
//			paramSelect.add(filter.getToDate());
//			countParams.add(filter.getToDate());
//		}
		
		selectSql.append(fromQuery).append(" ORDER BY ME.created_time DESC");
		countQuery.append("SELECT COUNT(*) AS COUNT ").append(fromQuery);

		String[] fieldNames = new String[] { 
				"id", "name", "address",
				"code", "description", "status", 
				"provinceId", "districtId", "wardId", 
				"provinceName", "districtName", "wardName",
				"createdUser","createdTime"
				};
		
		Type[] fieldTypes = new Type[] { 
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, StandardBasicTypes.STRING,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING, StandardBasicTypes.INTEGER,
				StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING, StandardBasicTypes.STRING,
				StandardBasicTypes.STRING, StandardBasicTypes.DATE };

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		synchronizedClass.add(Media.class);
		synchronizedClass.add(CmsUser.class);

		List<MediaSearchVO> list = null;
		if (filter.getPaging() != null) {
			list = repo.getListByQueryAndScalarPaginated(
					MediaSearchVO.class, fieldNames, fieldTypes,
					selectSql.toString(), countQuery.toString(), paramSelect,
					countParams, filter.getPaging(), synchronizedClass);
		} else {
			list = repo.getListByQueryAndScalar(MediaSearchVO.class,
					fieldNames, fieldTypes, selectSql.toString(), paramSelect,
					synchronizedClass);
		}

		return list;		
	} 
	/**
 	 * @author tuantm20
 	 * @throws DataAccessException
 	 */
 	@Override
	public List<Media> getAllMedias() throws DataAccessException {
		List<Media> list = null;
		if (list == null) {
			String sql = " select * "
					+ " from media "
					+ " where status != -1 "
					+ " order by LOWER(name) ";
			
			list = repo.getListBySQL(Media.class, sql, null);
		}
		return list;
	}
 	@Override
	public List<Media> getListMediasByCmsUserId(Integer id) throws DataAccessException{
		List<Object> params = new ArrayList<Object>();
		String sql = "SELECT * FROM MEDIA  where STATUS != ? AND CMS_USER_ID = ?";
		params.add(MediaStatus.DELETED.getValue());
		params.add(id);
		return repo.getListBySQL(Media.class, sql, params);
	}
 	@Override
	public Boolean checkNameExist(String userName) throws DataAccessException {
		StringBuilder sql = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		sql.append(" select count(1) count from dual where exists (select 1 from MEDIA where Upper(NAME_TEXT) = ? and status != ?)");
		params.add(userName.toUpperCase());
		params.add(MediaStatus.DELETED.getValue());
		int c = repo.countBySQL(sql.toString(), params);
		return c > 0 ? true : false;
	} 
 	@Override
	public Boolean checkCodeExist(String userName) throws DataAccessException {
		StringBuilder sql = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		sql.append(" select count(1) count from dual where exists (select 1 from MEDIA where Upper(CODE) = ? and status != ?)");
		params.add(userName.toUpperCase());
		params.add(MediaStatus.DELETED.getValue());
		int c = repo.countBySQL(sql.toString(), params);
		return c > 0 ? true : false;
	} 
 	@Override
	public Integer getIdFromName(String userName) throws DataAccessException {
		StringBuilder sql = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		sql.append(" select id from media where status != ? and name = ?");
		params.add(MediaStatus.DELETED.getValue());
		params.add(userName);
		int c = repo.executeSQLQuery(sql.toString(), params);
		return c;
	} 
 	@Override
	public Media getMediaFromName(String userName) throws DataAccessException {
		StringBuilder sql = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		sql.append(" select * from media m where m.status != ? and m.name = ?");
		params.add(MediaStatus.DELETED.getValue());
		params.add(userName);
		return repo.getEntityBySQL(Media.class, sql.toString(), params);
	} 
} 
 
